package com.example.dao;

import com.example.entity.StudentInfo;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;

@Repository
public interface StudentInfoDao extends Mapper<StudentInfo> {


    @Select("select * from student_info where name=#{name}")
    StudentInfo findByName(String name);

    @Select("select * from student_info where name=#{name} AND password= #{password}")
    StudentInfo findByNameAndPassword(@Param("name") String name,@Param("password") String password);

    @Select("select * from student_info where id=#{id}")
    StudentInfo findById(long id);

    @Update("UPDATE student_info " +
            "SET name = #{name}, sex = #{sex}, age = #{age}, code = #{code}, score = #{score}, xueyuanid = #{xueyuanid},zhuanyeid=#{zhuanyeid} ,status=#{status} ,password =#{password} " +" "+//这个空格必须加，否则会 报错
            "WHERE id = #{id}")
    void updateByid(@Param("id") long id,@Param("name")String name,@Param("sex") String sex,@Param("age") String age,@Param("code") String code,@Param("score") Integer score,@Param("xueyuanid") long xueyuanid
            ,@Param("zhuanyeid") Long zhuanyeid,@Param("status") String status,@Param("password") String password);

    @Select("select * from student_info")
    List<StudentInfo> findAllStudent();

    @Delete("DELETE FROM student_info WHERE id = #{id}")
    void deleteById(Long id);

    @Select("select  a.* ,b.name AS xueyuanName from student_info As a left join xueyuan_info As b on a.id = b.id")
    List<StudentInfo> findAllJoinXueyuan();

    @Select("select count(id)" +
            " from student_info")
    Integer getBiye();

    @Select("select count(id) from student_info " +
            "where status= '待业' ")
    Integer getDaiye();

    @Select("select count(id) from student_info where status= '已就业' ")
    Integer getJiuye();

    @Select("select count(id) from student_info where zhuanyeid=#{zhuanyeid}")
    Integer getstuNum(long id);

    @Select("select count(id) from student_info where zhuanyeid=#{zhuanyeid} and status= '已就业'")
    Integer getbiyeNum(long id);
}
